#!/usr/bin/python3
# -*- coding:utf-8 -*-
""""
   @Author: 即将拥有人鱼线的yjw
   @Time: 2023/11/14 11:09
   @File: OperationExcel.py
   @desc:
"""
import os.path
import re

import pandas as pd
import xlrd
from pyexcel import get_book
from openpyxl import load_workbook
from BladeFerm.utils.FileOerate import getFilePath
from qturboframe.lib.report.log import logger
from BladeFerm.utils.excel_operate import ExcelOfPandas


class OperationExcel:
    def __init__(self, xlsxName=None, sheetName=None, project_web_path=None):
        self.projectWebPath = project_web_path
        self.sheetName = sheetName
        self.xlsxName = xlsxName
        self.EP = ExcelOfPandas()
        self.sheet_names = []
        self.sheet_data = dict()
        if not self.xlsxName:
            filePath, filename = os.path.split(project_web_path)
            self.xlsxName = filename
            self.projectWebPath = filePath
        self.excelFilePath = os.path.join(self.projectWebPath, self.xlsxName)

    def getSheet(self):
        path = getFilePath(self.xlsxName, path=self.projectWebPath)
        logger.info(f'用例路径：{path}')
        book = xlrd.open_workbook(path)
        return book.sheet_by_name(self.sheetName)

    def getSheets(self):
        filename = os.path.join(self.projectWebPath, self.xlsxName)
        logger.info(f'用例路径：{filename}')
        if filename.endswith(".xlsx"):
            book = load_workbook(filename, read_only=True)  # read_only 可以提高读取性能
            sheet_names = book.sheetnames
        else:
            xls = pd.ExcelFile(filename)
            sheet_names = xls.sheet_names
        logger.info(f"sheet_names: {sheet_names}")
        return sheet_names

    def getExcelDatas(self):
        data = []
        sheet_data = self.getSheet()
        title = sheet_data.row_values(0)#获取表头
        for row in range(1, sheet_data.nrows):
            row_value = sheet_data.row_values(row)
            data.append(dict(zip(title, row_value)))
        return data

    def read_sheet_by_name(self, sheet_name=None):
        if sheet_name is None:
            sheet_name = self.sheetName
        df = self.EP.readExcel(self.excelFilePath, sheet_name, engine='openpyxl')
        # logger.info(f"read {self.sheetName} : {df}")
        self.sheet_data[sheet_name] = df
        return df

    @staticmethod
    def filter_row_by_col(col_name='数据编号', col_value='$D001', key='formatter(Object)', df: pd.DataFrame = None):
        filtered_df = df[df[col_name] == col_value]
        report_datagram_ids = filtered_df[key].values
        logger.info(f"{col_value}-{col_value}: {filtered_df}")
        return report_datagram_ids

    def readExcelDatas(self, sheet_name="用例"):
        """
        仅能用于读取blade导出的excel用例文件
        :return:
        """
        if not sheet_name:
            sheet_name = self.sheetName
        logger.info(f'用例路径：{self.excelFilePath}')
        self.EP.readExcel(self.excelFilePath, sheet_name, engine='openpyxl')
        first_value = self.EP.df.iloc[0, 0]
        if first_value == '流水号':
            self.EP.readExcel(self.excelFilePath, sheet_name, header=1, engine='openpyxl')
        self.EP.replace_nan()
        rows = self.EP.get_row_dict()
        logger.info(f'rows: {rows}')
        return rows

    @staticmethod
    def deal_aml_rc_basic_data_check_cases(data: list = None):
        """
        处理aml_rc_basic_data_check用例，
        将用例路径中的规则编号和行业类型提取出来，作为输入数据中的rules_no，并设置用例标签，判断用例类型，新增tables:_list|params
        预期结果新增：TOTAL:1
        :param data:
        :return:
        """
        pattern_path = r'/(\d{9})-.*基础数据检查'
        result = list()
        for line in data:
            case_path = line.get("用例路径")
            temp_res = re.search(pattern_path, case_path, re.I|re.M)
            if temp_res:
                tag_name = "信托" if re.search(r"-信托", case_path) else "基金"
                tag_name += ",指标基础数据检查"
                rule_no = temp_res.group(1)
                test_point = line.get("测试点")
                temp_input = f"tables:_list" if re.search(r"基础数据.*数据列表", test_point) else f"tables:params"
                temp_input = temp_input + "\nrules_no:" + rule_no
                input_data = line.get("输入数据")
                expect_data = line.get("预期结果")
                input_data = input_data + "\n" + temp_input if input_data else temp_input
                expect_data = expect_data + "\n" + "TOTAL:1" if expect_data else "TOTAL:1"
                line.update({"输入数据": input_data, "预期结果": expect_data, "菜单功能": tag_name})
                result.append(line)
        logger.info(f"deal_input_data: {result}")
        return result

    @staticmethod
    def deal_aml_ls_basic_data_check_cases(data: list = None, modes: list = None):
        """
        处理aml_ls_basic_data_check用例，
        将测试点中的规则编号提取出来，作为输入数据中的rules_no，并设置用例标签，判断用例类型，新增tables:_list|params
        预期结果新增：TOTAL:1
        :param data:
        :param modes:
        :return:
        """
        if not modes:
            modes = [1, 2, 3]
        result = list()
        for line in data:
            case_path = line.get("用例路径")
            pattern_path = r'可疑规则/([a-zA-Z0-9]+)/(.*?)/基础数据检查'
            temp_res = re.search(pattern_path, case_path, re.I | re.M)
            if temp_res:
                tag_name = "规则基础数据检查"
                test_point = line.get("测试点")
                rule_no = temp_res.group(1)
                case_dir_name = temp_res.group(2)
                pattern_version = re.match(r'(\d{4})-.*?', case_dir_name)
                rule_version = pattern_version.group(1) if pattern_version else None
                pattern_point = re.search(r'规则.*(列表|配置表)', test_point, re.I)
                mode = 0
                if pattern_point and pattern_point.group(1) == "列表":
                    temp_input = f"tables:_list"
                    step = f"检查可疑规则表基础数据列表中检查是否存在{rule_no}规则的SQL"
                    tables = ["hsamlls.amlls_cfg_rule_list", "hsamlls.amlls_cfg_rulesql_list",
                              "hsamlls.amlls_cfg_ruleparameter_list", "hsamlls.amlls_cfg_ruleproposal_list"]
                    temp_input = temp_input + "\nrules_no:" + rule_no
                    expect_row = f"检查存在{rule_no}规则对应的SQL\nTOTAL:1"
                    mode = 1
                elif pattern_point and pattern_point.group(1) == "配置表":
                    temp_input = "tables:_params"
                    step = f"检查可疑规则表基础数据配置表中检查是否存在{rule_no}规则的SQL"
                    tables = ["hsamlls.amlls_cfg_rule", "hsamlls.amlls_cfg_ruleparameter",
                              "hsamlls.amlls_cfg_ruleproposal", "hsamlls.amlls_cfg_rulesql"]
                    temp_input = temp_input + "\nrules_no:" + rule_no
                    expect_row = f"检查存在{rule_no}规则对应的SQL\nTOTAL:1"
                    mode = 2
                else:
                    tables = []
                    temp_input = ""
                    step = ""
                    expect_row = f"检查存在{rule_no}规则对应的SQL\n"
                    pattern_status = r'启用状态检查-(开启|不开启)'
                    temp_res2 = re.search(pattern_status, test_point, re.I | re.M)
                    if temp_res2:
                        step = f"执行sql语句，检查配置表amlls_cfg_rule中检查{rule_no}规则是否启用"
                        temp_input = f"SQL语句:select is_enabled from hsamlls.amlls_cfg_rule where rules_no='{rule_no}'"
                        if rule_version:
                            temp_input = temp_input + f" and rule_version='{rule_version}'"
                        is_enabled = 1 if temp_res2.group(1) == "开启" else 0
                        expect_row = expect_row + f"is_enabled:{is_enabled}"
                        mode = 3
                input_data = line.get("输入数据")
                expect_data = line.get("预期结果")
                input_data = input_data + "\n" + temp_input if input_data else temp_input

                expect_data = expect_data + "\n" + expect_row if expect_data else expect_row
                line.update({"菜单功能": tag_name,
                             "操作步骤": step,
                             "输入数据": input_data,
                             "预期结果": expect_data,
                             "数据设计说明": "待检查的相关表如下：\n" + ",".join(tables)})
                if step and mode in modes:
                    result.append(line)
        logger.info(f"deal_input_data: {result}")
        return result

    def read_aml_company_data(self):
        self.EP.readExcel(self.projectWebPath, self.sheetName, engine='openpyxl')
        logger.info(f"df: {self.EP.df}")
        rows = self.EP.get_row_dict()
        logger.info(f'read_aml_company_data : {rows}')
        return rows

    def write_data(self, data_list: list, case_mode: str = '_CASE'):
        '''
        写入excel
        :param case_mode:
        :param data_list:
        :return:
        '''
        fp, fn = os.path.split(self.projectWebPath)
        name, ext = os.path.splitext(fn)
        filepath = os.path.join(fp, name + case_mode + ext)
        index = ["流水号", "测试点", "优先级", "正反例", "前置条件", "用户（操作员）", "菜单功能",
                 "用例名称", "操作步骤", "输入数据", "预期结果", "数据设计说明", "界面期望检查", "关联需求单", "关联任务单", "用例路径"]
        df_data = self.EP.data_to_DF(data_list)
        self.EP.write_excel(filepath, df_data, sheet="用例", engine='openpyxl')


class ExcelDeal():
    def __init__(self, filename):
        self.book = get_book(file_name=filename)
        self.sheets = self.book.sheet_names()
        logger.info(f"sheets: {self.sheets}")
        self.sheet = None

    def readExcelDatas(self, sheetIndex=0):
        self.sheet = self.book.sheet_by_index(sheetIndex)
        results = list()
        first_row = self.sheet[0][0:5]
        for index, line in enumerate(self.sheet):
            if index > 1 and line[0]:
                row = dict(zip(first_row, line[0:5]))
                results.append(row)
        logger.info(f'result: {results}')
        return results


# 对excel表头进行全局变量定义
class ExcelVarles:
    case_name = '测试用例'
    case_data = '请求参数'
    case_type = '用例类型'
    case_result = '期望结果'
    case_before = '前置条件'
    case_describe = '步骤描述'
    case_path = '路径'


def getExcelData(xlsxName, sheetName, caseName, project_web_path):
    '''
    获取EXCEL用例数据
    '''
    logger.info(f"getExcelData: {xlsxName, sheetName, caseName, project_web_path}")
    data = []
    OP = OperationExcel(xlsxName, sheetName, project_web_path)
    results = OP.readExcelDatas()
    if results:
        for i in results:
            if caseName in i.values():
                data.append(i)
        return data
    else:
        return []


if __name__ == '__main__':
    filepath = "E:\AutoTest\Project\金融反洗钱项目\web应用\FastApi\static\AmlConfig\反洗钱5.0客户版本统计.xlsx"
    # OE = ExcelDeal(filepath)
    # OE.readExcelDatas()
    PATH_EXCEL = "E:\AutoTest\Project\金融反洗钱项目\web应用\数据预埋\基础功能\CRS管理\CRS规则校验\CRS报文规则校验用例1.xlsx"
    OPE = OperationExcel('', 1, PATH_EXCEL)
